﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BBD_DAL.Entities;
using BBD_DAL.DBConnector;
using System.Data;
using MySql.Data.MySqlClient;

namespace BBD_DAL.DAO
{
	public class QuestionnaireDao : IBaseDao<Questionnaire>
	{

		private DBConnection dbConnector;
		private Dictionary<string, string> queries;

		public QuestionnaireDao(string connectionString)
		{
			this.Initialize(connectionString);
		}

		public void Initialize(string connectionString)
		{
			this.dbConnector = new DBConnection(connectionString);
			queries = new Dictionary<string, string>();
			queries.Add("Get", "SELECT * FROM ankiety WHERE ankieta_id = @id;");
			queries.Add("GetBySchool", "SELECT * FROM ankiety WHERE szkola_id = @id;");
			queries.Add("Save", "UPDATE ankiety SET wiek = @age, plec = @sex, szkola_id = @sid WHERE ankieta_id = @id;");
			queries.Add("Insert", "INSERT INTO ankiety (wiek, szkola_id, plec) VALUES (@age, @sid, @sex);");
			queries.Add("Delete", "DELETE FROM ankiety WHERE id = @id;");
			queries.Add("DeleteAll", "DELETE FROM ankiety; ALTER TABLE ankiety AUTO_INCREMENT = 1;");
			queries.Add("List", "SELECT * FROM ankiety;");
		}

		public Questionnaire Get(long id)
		{
			DataTable data;
			this.dbConnector.RunQuery(this.queries["Get"], new MySqlParameter[]{
				new MySqlParameter(){ParameterName = "@id", Value = id, MySqlDbType = MySqlDbType.Int64}
			}, out data);

			List<Questionnaire> questionnaires = this.Convert(data);

			return questionnaires != null ? questionnaires[0] : null;
		}

		public List <Questionnaire> GetBySchool(long id)
		{
			DataTable data;
			this.dbConnector.RunQuery(this.queries["GetBySchool"], new MySqlParameter[]{
		        new MySqlParameter(){ParameterName = "@id", Value = id, MySqlDbType = MySqlDbType.Int64}
		    }, out data);

			List<Questionnaire> questionnaires = this.Convert(data);

			return questionnaires != null ? questionnaires : null;
		}


		public void Save(Questionnaire entity)
		{
			this.dbConnector.RunQuery(this.queries["Save"], new MySqlParameter[]{
				new MySqlParameter(){ParameterName = "@id", Value = entity.Id, MySqlDbType = MySqlDbType.Int64},
				new MySqlParameter(){ParameterName = "@age", Value = entity.Age, MySqlDbType = MySqlDbType.Int16},
				new MySqlParameter(){ParameterName = "@sid", Value = entity.School.Id, MySqlDbType = MySqlDbType.Int64},
				new MySqlParameter(){ParameterName = "@sex",Value = entity.Sex.GetSexCode(), MySqlDbType = MySqlDbType.VarChar}
			});
		}

		public Questionnaire Insert(Questionnaire entity)
		{
			long id;
			this.dbConnector.RunQuery(this.queries["Insert"], new MySqlParameter[]{				
				new MySqlParameter(){ParameterName = "@age", Value = entity.Age, MySqlDbType = MySqlDbType.Int16},
				new MySqlParameter(){ParameterName = "@sex", Value = entity.Sex.GetSexCode(), MySqlDbType = MySqlDbType.VarChar},
				new MySqlParameter(){ParameterName = "@sid", Value = entity.School.Id, MySqlDbType = MySqlDbType.Int64}
			}, out id);

			entity.Id = id;

			return entity;
		}

		public void Delete(long id)
		{
			this.dbConnector.RunQuery(this.queries["Delete"], new MySqlParameter[]{
				new MySqlParameter(){ParameterName = "@id", Value = id, MySqlDbType = MySqlDbType.Int64}
			});
		}

		public void Delete(Questionnaire entity)
		{
			this.Delete(entity.Id);
		}

		public void Delete()
		{
			this.dbConnector.RunQuery(this.queries["DeleteAll"], null);
		}

		public List<Questionnaire> List()
		{
			DataTable data;
			this.dbConnector.RunQuery(this.queries["List"], null, out data);
			return this.Convert(data);
		}

		private List<Questionnaire> Convert(DataTable data)
		{
			if (data == null || data.Rows == null || data.Rows.Count == 0)
				return null;

			List<Questionnaire> questionnaires = new List<Questionnaire>();
			foreach (DataRow row in data.Rows)
			{
				Questionnaire questionnaire = new Questionnaire();
				questionnaire.Id = Int64.Parse(row["ankieta_id"].ToString());
				//questionnaire.Age = row["wiek"] == null ? (short?)null : Int16.Parse(row["wiek"].ToString());// nie dziala dla nulli
				questionnaire.Age = System.DBNull.Value.Equals(row["wiek"]) ? (short?)null : Int16.Parse(row["wiek"].ToString());
				//questionnaire.Sex = row["plec"] == null ? Sex.NotDefined : row["plec"].ToString().ToLower().Equals("m") ? Sex.Male : Sex.Female;
				questionnaire.Sex = System.DBNull.Value.Equals(row["plec"]) ? Sex.NotDefined : row["plec"].ToString().ToLower().Equals("m") ? Sex.Male : Sex.Female;
				questionnaire.School = new School() { Id = Int64.Parse(row["szkola_id"].ToString()) };
				questionnaires.Add(questionnaire);
			}
			return questionnaires;
		}
	}
}
